/*
{
"状态":1,
"名称":"检查-是否按唯一码操作",
"作者":"马学兵",
"创建时间":"2019-10-18",
"功能":"获取-店铺信息",
"log": [
	{"date":"06-01" , "dev" :"hzhh" , "content" : ""}
]
}
*/

USE regent

declare @date date=dateadd(day,-1,getdate())

create table #T
(
	类型 nvarchar(50),
	编号 varchar(50),
	名称 nvarchar(100),
	单号 varchar(50),
	来源单号 varchar(50),
	时间 datetime,
	审核时间 datetime,
	操作人 nvarchar(50)
)

insert into #T(类型,编号,名称,单号,来源单号,时间,审核时间,操作人)
select'店铺收货',TinShop,Customer_na,ShopTinID,DeliveryID,Tin_Date,PostedDate,UserName 
from regent..ShopTin A
left join regent..Customer B on A.TinShop=B.Customer_id
left join regent..[right] C on A.Operator=C.UserNo
where Posted=1 and Type=1 
and exists(select 1 from regent..GoodsLabelBill where AccBillNo=DeliveryID)
and not exists(select 1 from regent..GoodsLabelBill where AccBillNo=ShopTinID)
and  TinShop not in('EIWD','EITB')

insert into #T(类型,编号,名称,单号,来源单号,时间,审核时间,操作人)
select '仓库收退货',A.Warehouse_No,Warehouse_Na,PureceiptID,DealereturnID,Receipt_Date,PostedDate,UserName 
FROM regent.dbo.Pureceipt A
left join regent.dbo.Warehouse B on A.Warehouse_No=B.Warehouse_No
left join regent.dbo.[right] C on A.Operator=C.UserNo
where Posted=1 and Type=3
and exists(select 1 from regent.dbo.GoodsLabelBill where AccBillNo=DealereturnID)
and not exists(select 1 from regent.dbo.GoodsLabelBill where AccBillNo=PureceiptID)
and Customer_id not in('EIWD','EITB')

insert into #T(类型,编号,名称,单号,来源单号,时间,审核时间,操作人)
select '店铺调入',A.TinShop,B.Customer_na,A.ShopTinID,A.ShopToutID,Tin_Date,PostedDate,UserName 
from regent.dbo.ShopTin A
left join regent.dbo.Customer B on A.TinShop=B.Customer_id 
left join regent.dbo.[right] C on A.Operator=C.UserNo
where Posted=1 and Type=2
and exists(select 1 from regent.dbo.GoodsLabelBill where AccBillNo=ShopToutID)
and not exists(select 1 from regent.dbo.GoodsLabelBill where AccBillNo=ShopTinID)

insert into #T(类型,编号,名称,单号,来源单号,时间,审核时间,操作人)
SELECT '仓库调入',A.Warehouse_No,Warehouse_Na,A.PureceiptID,A.DeliveryID,A.Receipt_Date,PostedDate,UserName 
FROM regent.dbo.PuReceipt A
left join regent.dbo.Warehouse B on A.Warehouse_No=B.Warehouse_No
left join regent.dbo.[right] C on A.Operator=C.UserNo
WHERE Posted=1 and type=2
and exists(select 1 from regent.dbo.GoodsLabelBill where AccBillNo=DeliveryID)
and not exists(select 1 from regent.dbo.GoodsLabelBill where AccBillNo=PureceiptID)

insert into #T(类型,编号,名称,单号,来源单号,时间,审核时间,操作人)
select '销售单',A.Customer_ID,Customer_na,CheckID,'',CheckDate,PostedDate,
isnull((
select top 1 H.Name from regent.dbo.CheckGoods G  
left join regent.dbo.BuisnessMan H on G.BusiManID=H.BuisnessManID
where G.checkid=A.checkid
),E.username )As
UserName from [check] A 
left join regent.dbo.Customer D on A.Customer_ID=D.Customer_id
left join regent.dbo.[right] E on A.Operator=E.UserNo
where Posted=1 and CheckDate>=@date
and A.Customer_id not in('EIWD','EITB')
and not  exists(select 1 from regent.dbo.GoodsLabelBill where AccBillNo=checkid)
and exists(select 1 from regent.dbo.CheckGoods B where CheckID=A.checkid 
and exists(select 1 from regent.dbo.GoodsLabel C where Goods_No=B.goods_no))


select 类型,编号,名称,单号,时间,操作人 from #T order by 类型

drop table #T 

